package com.ocm.tourism.core.jdbc;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

@Component
public class CommonSQLCommand implements SQLCommand {

   private final static Map<String, String> LIST_SQL_COMMANDS;

   private String databaseType = "postgres";

   static {
      Map<String, String> sqlCommand = new HashMap<String, String>();
      sqlCommand.put("select_SaleOff_Hotels", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, hotel.* from \"LISTHOTELS\" hotel) SELECT * FROM query WHERE row_nr BETWEEN (:from) AND (:to)");
      sqlCommand.put("select_Adv_Restaurants", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, alias.* from \"LISTRESTAURANTS\" alias) SELECT * FROM query WHERE row_nr BETWEEN (:from) AND (:to)");
      sqlCommand.put("select_Adv_Coffees", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, alias.* from \"LISTCOFFEES\" alias) SELECT * FROM query WHERE row_nr BETWEEN (:from) AND (:to)");
      sqlCommand.put("select_Adv_FoodStalls", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, alias.* from \"LISTFOODSTALLS\" alias) SELECT * FROM query WHERE row_nr BETWEEN (:from) AND (:to)");
      sqlCommand.put("select_Adv_FoodStalls_By_PeriodTime", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, alias.* from \"LISTFOODSTALLS\" alias) SELECT * FROM query WHERE  bus_periodtime IN (:periodtimes) AND (row_nr BETWEEN (:from) AND (:to))");
      sqlCommand.put("select_Adv_TourAgencyAdvs", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, alias.* from \"LISTTOURISTAGENCYADVS\" alias) SELECT * FROM query WHERE sad_tour_type =:tourtype and sad_ad_priority IN (:priorities) AND (row_nr BETWEEN (:from) AND (:to))");
      sqlCommand.put("select_Adv_TourAgencies", "WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as row_nr, alias.* from \"LISTTOURISTAGENCIES\" alias) SELECT * FROM query WHERE sad_tour_type =:tourtype AND (row_nr BETWEEN (:from) AND (:to))");
      sqlCommand.put("select_businesses_viewest", "SELECT * from \"LISTBUSINESSVIEWEST\" WHERE bus_type IN (:businessTypes)");
      sqlCommand.put("count_Adv_Hotels", "SELECT count(*) as count from \"LISTHOTELS\"");
      sqlCommand.put("count_Adv_FoodStalls", "SELECT count(bus_id) as count from \"LISTFOODSTALLS\"");
      sqlCommand.put("count_Adv_TourAgencies", "SELECT count(bus_id) as count from \"LISTTOURISTAGENCIES\" WHERE sad_tour_type =:tourtype");
      sqlCommand.put("count_Adv_FoodStalls_By_PeriodTime", "SELECT count(bus_id) as count from \"LISTFOODSTALLS\" WHERE bus_periodtime IN (:periodtimes)");
      LIST_SQL_COMMANDS = sqlCommand;
   }

   public String getDatabaseType() {
      return databaseType;
   }

   public void setDatabaseType(String databaseType) {
      this.databaseType = databaseType;
   }

   public String convertLongParaValues(List<Long> values) {
      StringBuilder convertPara = new StringBuilder();
      Iterator<Long> entryIter = values.iterator();
      int k = 0;
      while (entryIter.hasNext()) {
         if (k > 0){
            convertPara.append(",");
         }
         k++;
         convertPara.append(entryIter.next());
      }
      return convertPara.toString();
   }

   @Override
   public String getSaleOffHotels() {
      String sql = LIST_SQL_COMMANDS.get("select_SaleOff_Hotels");
      return sql;
   }

   @Override
   public String getRestaurantAdvs() {
      String sql = LIST_SQL_COMMANDS.get("select_Adv_Restaurants");
      return sql;
   }

   @Override
   public String getCoffeeAdvs() {
      String sql = LIST_SQL_COMMANDS.get("select_Adv_Coffees");
      return sql;
   }

   @Override
   public String getFoodStallAdvs() {
      String sql = LIST_SQL_COMMANDS.get("select_Adv_FoodStalls");
      return sql;
   }

   @Override
   public String getFoodStallAdvsByPeriodTime() {
      String sql = LIST_SQL_COMMANDS.get("select_Adv_FoodStalls_By_PeriodTime");
      return sql;
   }

   @Override
   public String countFoodStallAdvsByPeriodTime() {
      String sql = LIST_SQL_COMMANDS.get("count_Adv_FoodStalls_By_PeriodTime");
      return sql;
   }

   @Override
   public String countFoodStallAdvs() {
      String sql = LIST_SQL_COMMANDS.get("count_Adv_FoodStalls");
      return sql;
   }

   @Override
   public String getTourAgencyAdvs() {
      String sql = LIST_SQL_COMMANDS.get("select_Adv_TourAgencyAdvs");
      return sql;
   }
   
   @Override
   public String getTourAgencies() {
      String sql = LIST_SQL_COMMANDS.get("select_Adv_TourAgencies");
      return sql;
   }

   @Override
   public String countTourAgencies() {
      String sql = LIST_SQL_COMMANDS.get("count_Adv_TourAgencies");
      return sql;
   }

   @Override
   public String countHotelAdvs() {
      String sql = LIST_SQL_COMMANDS.get("count_Adv_Hotels");
      return sql;
   }

   @Override
   public String getBusinessesViewestByBusinessTypes() {
      String sql = LIST_SQL_COMMANDS.get("select_businesses_viewest");
      return sql;
   }
}
